# coding: utf8

import pandas as pd


class Preliminary:

    @staticmethod
    def write_excel():
        df1 = pd.DataFrame([['李明', 15, '游泳'], ['张力', 25, '绘画']],
                           index=['1001', '1002'],
                           columns=['name', 'age', 'speciality'])
        df2 = pd.DataFrame([['何正', 18, '象棋'], ['孟凡', 22, '竞走']],
                           index=['100p3', '1004'],
                           columns=['name', 'age', 'speciality'])
        df3 = pd.DataFrame([['赵彤', 28, '外语'], ['辛里', 12, '漫画']],
                           index=['1005', '1006'],
                           columns=['name', 'age', 'speciality'])

        df1.to_excel("stu55a.xlsx", header=None, index=None)

        df1.to_excel('stu55b.xlsx')

        df1.to_excel('stu55c.xlsx', sheet_name='Sheet_student')

        with pd.ExcelWriter('stu55d.xlsx') as writer:
            df1.to_excel(writer, sheet_name='Sheet_student_1')
            df2.to_excel(writer, sheet_name='Sheet_student_2')

        with pd.ExcelWriter('stu55d.xlsx', mode='a') as writer:
            df3.to_excel(writer, sheet_name='Sheet_student_3')

    @staticmethod
    def read_excel(read_file="stu55a.xlsx"):
        df = pd.read_excel(read_file, header=0, names=["name", "age", "spec"])
        print(df)


def task():
    read_excel_file = 'stu55a.xlsx'
    df = pd.read_excel(read_excel_file, header=None, names=["name", "age", "speciality"])
    df = df.astype({'age': int})
    df_lt20 = df.loc[df.age < 20]
    df_gt20 = df.loc[df.age > 20]

    # write df to excel.sheet
    df.to_excel("stu55e.xlsx", sheet_name="sheet_all", index=None)
    with pd.ExcelWriter("stu55e.xlsx", mode='a') as writer:
        df_lt20.to_excel(writer, sheet_name='sheet_lt20', index=None)
        df_gt20.to_excel(writer, sheet_name='sheet_gt20', index=None)


def expand_train():
    df_lt20 = pd.read_excel("stu55e.xlsx", 'sheet_lt20', index_col=None)
    df_lt20.to_excel("stu55f.xlsx", header=['姓名', '年龄', '特长'], index=None)
    print(pd.read_excel("stu55f.xlsx"))


if __name__ == '__main__':
    # Preliminary.write_excel()
    # Preliminary.read_excel()
    # task()
    expand_train()
